﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace InsideDL.MISData
{
    public class DataIMImport:DataMatrix
    {
        StringBuilder insertSB = null;
        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="p_sht">要匯入的sheet name</param>
        /// <param name="p_path">Excel 檔案路徑</param>
        /// <returns></returns>
        public string buildInsertStatement(string p_sht,string p_path)
        {
            //    ,[{0}單月]
            string mon, sql,v1,v2,del;
            mon = p_sht.Substring(p_sht.Length - 2);
            v2 = mon.Equals("01") ? "餘額":"單月";                
            v1 =  "[{0}" + string.Format("{0}]",v2) ;
            v1 = string.Format(v1, p_sht);
            sql = string.Format(@"
SELECT {0} AS YYYYMM
 ,[科目代號]
    ,[科目名稱]
    ,[分類]
    ,[資產區隔]
    ,[項目]
    ,[會計科目]
    ,[會計科目明細]
    ,{1}
    ,[異動明細]
    ,[NOTE]
    ,[{0}餘額]
FROM [{0}$]
WHERE [科目代號] IS NOT NULL ", p_sht, v1);
            

            del = string.Format("DELETE FROM [IM001] WHERE [YYYYMM] = '{0}' ",p_sht);

            DataTable dt = getExcelData(sql,p_path);
            //0         1               2           3       4           5       6           7               8           9       10          
            //([YYYYMM],[科目代號],[科目名稱],[分類],[資產區隔],[項目],[會計科目],[會計科目明細],[餘額],,[異動明細],[NOTE],verify)
            sql = "INSERT INTO [IM001] VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',{8},'{9}','{10}',{11})";
            insertSB = new StringBuilder();
            insertSB.Append("USE [MISDB]" + System.Environment.NewLine);
            insertSB.Append("GO" + System.Environment.NewLine);
            insertSB.Append(del + System.Environment.NewLine);
            

            List<string> rowList = null;
            string str = string.Empty;
            foreach (DataRow row in dt.Rows)
            {
                rowList = new List<string>();
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    str = row[i].ToString();
                    if (i.Equals(8) || i.Equals(11))
                    {
                        str = string.IsNullOrEmpty(str)? "0" :str;
                    }
                    
                    //rowList.Add(row[i].ToString());
                    rowList.Add(str);
                }
                insertSB.AppendFormat(sql , rowList.ToArray());
                insertSB.Append(System.Environment.NewLine);
            }
            return insertSB.ToString();
        }
    }
}
